﻿/********************************************
 * MySql数据库接口
 * 2014-1-15
 * 肖俊 （abc195@qq.com）
 * 本 C# Mysql类库节省资源、高效，且方法全面，并避免了MySqlClient中的一个BUG
 *******************************************/

using System;
using System.Data;
using MySql.Data.MySqlClient;



    public class MysqlHelper
    {
        /// <summary>
        /// 数据库连接串
        /// </summary>
        private static string ConnString = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConn"].ToString();
         //private static readonly string ConnString= RM.Common.DotNetConfig.ConfigHelper.GetAppSettings("Mysql_oa");
        /// <summary>
        /// 数据库连接
        /// </summary>
        private static MySqlConnection Conn;
        /// <summary>
        /// 错误信息
        /// </summary>
        public static string ErrorString = "";
        /// <summary>
        /// 超时（秒）
        /// </summary>
        public static int TimeOut = 5;
        /// <summary>
        /// 初始化数据库链接
        /// </summary>
        /// <param name="connString">数据库链接</param>
        //public MysqlHelper(string connString)
        //{
        //    ConnString = connString;
        //    ConnTo();
        //}
        /// <summary>
        /// 执行sql返回DataTable
        /// </summary>
        /// <param name="SqlString">SQL语句</param>
        /// <param name="parms">Sql参数</param>
        /// <returns>DataTable</returns>
        public static DataTable ExecuteDataTable(string SqlString, MySqlParameter[] parms)
        {
            if (Conn == null || Conn.State != ConnectionState.Open)
                ConnTo();
            try
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = Conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SqlString;
                cmd.CommandTimeout = TimeOut;
                if (parms != null)
                    foreach (MySqlParameter pram in parms)
                        cmd.Parameters.Add(pram);
                DataTable dt = new DataTable();
                //dt.Load(cmd.ExecuteReader());
                MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
                sda.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {

                AddError(e.Message, SqlString);
                return null;
            }
            finally
            {
                if (Conn != null)
                {
                    if (Conn.State != ConnectionState.Closed)
                    {
                        Conn.Close();
                    }
                }
                GC.Collect();
            }

            ////创建一个MySqlCommand对象
            //MySqlCommand cmd = new MySqlCommand();
            ////创建一个MySqlConnection对象
            //MySqlConnection conn = new MySqlConnection(ConnString);
            ////在这里我们用一个try/catch结构执行sql文本命令/存储过程，
            ////因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，
            //try
            //{
            //    //调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数
            //    PrepareCommand(cmd, conn, null, CommandType.Text, SqlString, parms);
            //    //调用 MySqlCommand 的 ExecuteReader 方法
            //    MySqlDataAdapter adapter = new MySqlDataAdapter();
            //    adapter.SelectCommand = cmd;
            //    DataTable dt = new DataTable();
            //    adapter.Fill(dt);
            //    //清除参数
            //    cmd.Parameters.Clear();
            //    conn.Close();
            //    return dt;
            //}
            //catch (Exception e)
            //{
            //    AddError(e.Message, SqlString);
            //    return null;
            //}
            //finally
            //{
            //    if (conn != null) {
            //        if (conn.State != ConnectionState.Closed) {
            //            conn.Close();
            //        }
            //    }
            //    GC.Collect();
            //}
        }


        /// <summary>
        /// 准备执行一个命令
        /// </summary>
        /// <param name="cmd">sql命令</param>
        /// <param name="conn">OleDb连接</param>
        /// <param name="trans">OleDb事务</param>
        /// <param name="cmdType">命令类型例如 存储过程或者文本</param>
        /// <param name="cmdText">命令文本,例如:Select * from Products</param>
        /// <param name="cmdParms">执行命令的参数</param>
        private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = cmdType;
            if (cmdParms != null)
            {
                foreach (MySqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        /// <summary>
        /// 返回第一行
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public static DataRow ExecuteDataTableRow(string SqlString, MySqlParameter[] parms)
        {
            if (Conn == null || Conn.State != ConnectionState.Open)
                ConnTo();
            try
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = Conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SqlString;
                cmd.CommandTimeout = TimeOut;
                if (parms != null)
                    foreach (MySqlParameter pram in parms)
                        cmd.Parameters.Add(pram);
                DataTable dt = new DataTable();
                dt.Load(cmd.ExecuteReader());
                if (dt.Rows.Count > 0)
                    return dt.Rows[0];
            }
            catch (Exception e)
            {
                AddError(e.Message, SqlString);
            }
            return null;
        }
        /// <summary>
        /// 返回第一个值
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public static string ExecuteFirst(string SqlString, MySqlParameter[] parms)
        {
            if (Conn == null || Conn.State != ConnectionState.Open)
                ConnTo();
            try
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = Conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SqlString;
                cmd.CommandTimeout = TimeOut;
                if (parms != null)
                    foreach (MySqlParameter pram in parms)
                        cmd.Parameters.Add(pram);
                MySqlDataReader ss = cmd.ExecuteReader();
                string xx = "";
                if (ss.Read())
                    xx = ss[0].ToString();
                ss.Close();
                return xx;
            }
            catch (Exception e)
            {
                AddError(e.Message, SqlString);
            }
            finally
            {
                if (Conn != null)
                {
                    if (Conn.State != ConnectionState.Closed)
                    {
                        Conn.Close();
                    }
                }
                GC.Collect();
            }
            return null;

            ////创建一个MySqlCommand对象
            //MySqlCommand cmd = new MySqlCommand();
            ////创建一个MySqlConnection对象
            //MySqlConnection conn = new MySqlConnection(ConnString);
            ////在这里我们用一个try/catch结构执行sql文本命令/存储过程，
            ////因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，
            //try
            //{
            //    //调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数
            //    PrepareCommand(cmd, conn, null, CommandType.Text, SqlString, parms);
            //    //调用 MySqlCommand 的 ExecuteReader 方法
            //    MySqlDataReader ss = cmd.ExecuteReader();
            //    string xx = "";
            //    if (ss.Read())
            //        xx = ss[0].ToString();

            //    //清除参数
            //    cmd.Parameters.Clear();
            //    conn.Close();
            //    return xx;
            //}
            //catch (Exception e)
            //{
            //    AddError(e.Message, SqlString);
            //    return null;
            //}
            //finally
            //{
            //    if (conn != null)
            //    {
            //        if (conn.State != ConnectionState.Closed)
            //        {
            //            conn.Close();
            //        }
            //    }
            //    GC.Collect();
            //}
        }
        /// <summary>
        /// 返回第一个值
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public static long ExecuteInsertId(string SqlString, MySqlParameter[] parms)
        {
            if (Conn == null || Conn.State != ConnectionState.Open)
                ConnTo();
            try
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = Conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SqlString;
                cmd.CommandTimeout = TimeOut;
                if (parms != null)
                    foreach (MySqlParameter pram in parms)
                        cmd.Parameters.Add(pram);
                cmd.ExecuteNonQuery();
                return cmd.LastInsertedId;
            }
            catch (Exception e)
            {
                AddError(e.Message, SqlString);
            }
            return 0;
        }

        /// <summary>
        /// 执行无返回SQL语句
        /// </summary>
        /// <param name="SqlString">SQL语句</param>
        /// <param name="parms">Sql参数</param>
        ///<returns>是否执行成功</returns>
        public static bool ExecuteNonQuery(string SqlString, MySqlParameter[] parms)
        {
            if (Conn == null || Conn.State != ConnectionState.Open)
                ConnTo();
            try
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = Conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SqlString;
                cmd.CommandTimeout = TimeOut;
                if (parms != null)
                    foreach (MySqlParameter pram in parms)
                        cmd.Parameters.Add(pram);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                AddError(e.Message, SqlString);
                return false;
            }
        }

        /// <summary>
        /// 查询是否存在
        /// </summary>
        /// <param name="SqlString">SQL语句</param>
        /// <param name="parms">SQL参数</param>
        /// <returns>是否存在</returns>
        public static bool ExecuteExists(string SqlString, MySqlParameter[] parms)
        {
            if (Conn == null || Conn.State != ConnectionState.Open)
                ConnTo();
            try
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = Conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SqlString;
                cmd.CommandTimeout = TimeOut;
                if (parms != null)
                    foreach (MySqlParameter pram in parms)
                        cmd.Parameters.Add(pram);
                DataTable dt = new DataTable();
                dt.Load(cmd.ExecuteReader());
                if (dt.Rows.Count > 0)
                    return true;
                return false;
            }
            catch (Exception e)
            {
                AddError(e.Message, SqlString);
                return false;
            }
        }

        public static void GetSelfConn(string name)
        {
            if (name == "")
            {
                ConnString = System.Configuration.ConfigurationManager.ConnectionStrings[name].ToString();
            }
            else
            {
                ConnString = System.Configuration.ConfigurationManager.ConnectionStrings["MySQL"].ToString();
            }
        }

        /// <summary>
        /// 连接数据库
        /// </summary>
        private static void ConnTo()
        {
            Close();
            try
            {
                Conn = new MySqlConnection(ConnString);
                Conn.Open();
            }
            catch (Exception e)
            {
                ErrorString = "数据库连接错误：" + e.Message + "\r\n连接串：" + ConnString + "\r\n";
            }
        }
        /// <summary>
        /// 错误信息
        /// </summary>
        /// <param name="message"></param>
        /// <param name="sql"></param>
        private static void AddError(string message, string sql)
        {
            ErrorString = "数据库执行错误：" + message + "\r\nSQL语句：" + sql + "\r\n";
            if (!string.IsNullOrEmpty(ErrorString))
            {
                ErrorString = "";
            }
        }

        /// <summary>
        /// 关闭数据库链接
        /// </summary>
        public static void Close()
        {
            try
            {
                Conn.Close();
                Conn = null;
            }
            catch
            {
            }
        }

    }
